<?php
/**
 * PHPOrm
 * User: hongboli
 * Date: 14-10-22
 * Time: 上午10:35
 */

class PHPOrm
{
    public $db;
    private $order;
    private $limit;
    public $last_sql = '';
    public $table_name = '';
    public $db_name = '';
    public $cache = NULL;
    public $error;

    public function __construct($cache = NULL)
    {
        if ($cache) {
            $this->cache = $cache;
        }
        if (empty($this->cache)) {
            $this->cache = new Orm_Cache_Null();
        }
        if (empty($this->table_name)) {
            $this->table_name = get_class($this);
        }

        $this->order = array();
        $this->limit = 0;
    }

    /**
     * 初始化
     * @param $tableName string 数据库名.表名
     * @param string $dbInst
     * @param object $cache
     * @return PHPOrm
     * @throws ORM_DB_Exception
     */
    public static function init($tableName, $dbInst = '', $cache = NULL)
    {
        $model = new PHPOrm($cache);
        $dbTable = explode('.', $tableName);
        if (count($dbTable) == 1) {
            $model->table_name = $dbTable[0];
        } else if (count($dbTable) == 2) {
            $model->table_name = $dbTable[1];
            if (empty($dbInst)) {
                $model->setDB($dbTable[0]);
            }
        }

        if (!empty($dbInst)) {
            $model->setDB($dbInst);
        }
        return $model;
    }

    /**
     * 获取数据库实例
     * @return mixed
     */
    function get_db()
    {
        return $this->db;
    }

    /**
     * 设置数据库实例
     * @param $dbInst
     * @throws ORM_DB_Exception
     */
    function setDB($dbInst)
    {
        global $db;
        if (is_object($dbInst)) {
            $this->db = $dbInst;
        } else if (isset($db[$dbInst])) {
            $this->db = $db[$dbInst];
            $this->db_name = $dbInst;
        } else {
            throw new ORM_DB_Exception('对应的db连接[$dbInst]不存在!');
        }
    }

    /**
     * 插入一条数据
     * @param $item
     * @return int 插入的ID
     * @throws ORM_DB_Exception
     */
    function insert($item)
    {
        $columns = $this->getColumn(false);
        $columns_name = array();

        $sql_value = '';
        $item_count = 0;
        foreach ($columns as $k => $c) {
            // 如果是自增的字段的话，则跳过该字段
            if ($c->extra == 'auto_increment') {
                continue;
            }

            // 如果该字段非空且没有设置默认值的话，则必须传值
            // 注意：$c->default === null这里要恒等于，如果是==的话，那么字段设置为空的话也会被认为是没有设置默认值
            if (!isset($item[$c->oname])) {
                if ($c->nullable == false && $c->default === null) {
                    //非空且无默认
                    throw new ORM_DB_Exception("column {$c->name} 's value cannot be null!");
                } else {
                    continue;
                }
            }
            $value = $item[$c->oname];

            // 所有的字段名
            $columns_name[] = $c->name;

            // 如果是主键的话，则不允许为空
            if ($c->pk && empty($value)) {
                throw new ORM_DB_Exception("{$c->name} is key and cannot be null!");
            }

            $sql_value .= ($item_count > 0 ? ',' : '') . $this->escapeValue($value);
            $item_count++;
        }
        $keys = implode(',', $columns_name);
        $sql = "INSERT INTO {$this->table_name}( $keys ) VALUES( $sql_value );";
        try {
            $this->db->update($sql);
            $this->last_sql = $sql;
            return $this->db->lastID();
        } catch (Exception $e) {
            $this->error = $e->getMessage();
            throw new ORM_DB_Exception($e->getMessage() . " sql:" . $sql);
        }
    }

    /**
     * 批量插入多条数据
     * @param $datas
     * @return int 新增的条数
     * @throws ORM_DB_Exception
     */
    function batch_insert($datas)
    {
        if (!isset($datas[0])) {
            $datas = [$datas];
        }

        // 获取所有字段名
        $columns = $this->getColumn(false);
        $columns_name = array();

        // 获取数据
        $values = array();
        foreach ($datas as $item) {
            $item_count = 0;
            $sql_value = '';
            foreach ($columns as $k => $c) {
                // 如果是自增的字段的话，则跳过该字段
                if ($c->extra == 'auto_increment') {
                    continue;
                }

                // 如果该字段非空且没有设置默认的话，则必须传值
                // 注意：$c->default === null这里要恒等于，如果是==的话，那么字段设置为空的话也会被认为是没有设置默认值
                if (!isset($item[$c->oname])) {
                    if ($c->nullable == false && $c->default === null) {
                        // 非空且无默认
                        throw new ORM_DB_Exception("column {$c->name} 's value cannot be null!");
                    } else {
                        continue;
                    }
                }
                $value = $item[$c->oname];

                // 所有的字段名
                $columns_name[$k] = $c->name;

                // 如果是主键的话，则不允许为空
                if ($c->pk && empty($value)) {
                    throw new ORM_DB_Exception("{$c->name} is key and cannot be null!");
                }
                $sql_value .= ($item_count > 0 ? ',' : '') . $this->escapeValue($value);
                $item_count++;
            }
            $values[] = '(' . $sql_value . ')';
        }

        $sql = "INSERT INTO {$this->table_name}( " . implode(',', $columns_name) . " ) VALUES " . implode(',', $values);
        try {
            $this->db->update($sql);
            $this->last_sql = $sql;
            return count($values);
        } catch (Exception $e) {
            $this->error = $e->getMessage();
            throw new ORM_DB_Exception($e->getMessage() . " sql:" . $sql);
        }
    }

    /**
     * 删除数据
     * @param $where
     * @return bool
     */
    function delete($where)
    {
        $columns = $this->getColumn();
        $sql_where = '';
        $where_count = 0;
        foreach ($columns as $v) {
            if (isset($where[$v->name])) {
                $where_count++;
                $value = $where[$v->name];
                $sql_where .= " {$v->name} = " . $this->escapeValue($value) . " AND";
            }
        }
        if ($where_count > 0) {
            $sql_where = substr($sql_where, 0, -3); //去除最后的AND
        } else {
            $this->error = "where cannot be null in update sql";
            return false;
        }

        $sql = " DELETE FROM  {$this->table_name} WHERE $sql_where ";
        try {
            $rtn = $this->db->update($sql);
            $this->last_sql = $sql;
            return $rtn;
        } catch (Exception $e) {
            $this->error = $e->getMessage();
            return false;
        }
    }

    /**
     * 清空缓存
     */
    function flushCache()
    {
        $key = "orm_column_" . $this->table_name;
        $this->cache->setData($key, null);
    }


    /**
     * 获取所有字段属性
     * @param bool $cacheFlag 是否使用缓存的字段 默认是
     * @return array(Column)
     */
    function getColumn($cacheFlag = true)
    {
        $key = "orm_column_" . $this->db_name . '_' . $this->table_name;
        $columns = $this->cache->getData($key);
        if (!$cacheFlag || empty($columns)) {
            $sql = "SHOW COLUMNS FROM " . $this->table_name;
            $rtn = $this->db->getAll($sql);
            $columns = array();
            foreach ($rtn as $v) {
                $c = new Orm_Column($v);
                array_push($columns, $c);
            }
            $this->cache->setData($key, $columns);
        }
        return $columns;
    }

    /**
     * 更新数据
     * @param $item
     * @param null $where
     * @return bool
     * @throws ORM_DB_Exception
     */
    function update($item, $where = null)
    {
        if (empty($where)) {
            return $this->updateModel($item);
        }

        $columns = $this->getColumn();
        $sql_value = '';
        $sql_where = '';
        $value_count = 0;
        $where_count = 0;
        foreach ($columns as $v) {
            if (isset($item[$v->oname])) {
                $value_count++;
                $value = $item[$v->oname];
                $sql_value .= " {$v->name} = " . $this->escapeValue($value) . ',';
            }

            if (isset($where[$v->name])) {
                $where_count++;
                $value = $where[$v->name];
                $sql_where .= " {$v->name} = " . $this->escapeValue($value) . " AND";
            }
        }
        if ($value_count > 0) {
            $sql_value = substr($sql_value, 0, -1); //去除最后的逗号
        } else {
            $this->error = "nothing to be update";
            return false;
        }
        if ($where_count > 0) {
            $sql_where = substr($sql_where, 0, -3); //去除最后的AND
        } else {
            $this->error = "where cannot be null in update sql";
            return false;
        }

        $sql = " UPDATE {$this->table_name} SET $sql_value WHERE $sql_where ";
        try {
            $rtn = $this->db->update($sql);
            $this->last_sql = $sql;
            return $rtn;
        } catch (Exception $e) {
            $this->error = $e->getMessage();
            return false;
        }
    }

    /**
     * 当不指定where的时候则使用pki
     * @param $item
     * @return bool
     * @throws ORM_DB_Exception
     */
    function updateModel($item)
    {
        $columns = $this->getColumn();

        $sql_value = '';
        $sql_where = '';
        $value_count = 0;
        $where_count = 0;
        foreach ($columns as $v) {
            if ($v->pk) {
                if (isset($item[$v->oname])) {
                    $where_count++;
                    $value = $item[$v->oname];
                    $sql_where .= " {$v->name} = " . $this->escapeValue($value) . " AND";
                }
            } else {
                if (isset($item[$v->oname])) {
                    $value_count++;
                    $value = $item[$v->oname];
                    $sql_value .= " {$v->name} = " . $this->escapeValue($value) . ',';
                }
            }
        }
        if ($value_count > 0) {
            $sql_value = substr($sql_value, 0, -1); //去除最后的逗号
        } else {
            throw new ORM_DB_Exception("nothing to be update");
        }
        if ($where_count > 0) {
            $sql_where = substr($sql_where, 0, -3); //去除最后的AND
        } else {
            throw new ORM_DB_Exception("where cannot be null in update sql");
        }

        $sql = " UPDATE {$this->table_name} SET $sql_value WHERE $sql_where ";
        if ($this->debug) {
            var_dump($columns);
            var_dump($item);
            var_dump($sql);
        }
        try {
            return $this->db->update($sql);
        } catch (Exception $e) {
            $this->error = $e->getMessage();
            return false;
        }
    }

    /**
     * @param $item
     * @return mixed
     * @throws ORM_DB_Exception
     */
    function replace($item)
    {
        $columns = $this->getColumn();
        $sql_value = '';
        $columns_name = array();
        $item_count = 0;
        foreach ($columns as $k => $c) {
            if ($c->extra == 'auto_increment') {
                continue;
            }
            $value = $item[$c->oname];
            if (!isset($item[$c->oname])) {
                // 如果该字段非空且没有设置默认值的话，则必须传值
                // 注意：$c->default === null这里要恒等于，如果是==的话，那么字段设置为空的话也会被认为是没有设置默认值
                if ($c->nullable == false && $c->default === null) {
                    //非空且无默认
                    throw new ORM_DB_Exception("column {$c->name} 's value cannot be null!");
                } else {
                    continue;
                }
            }
            if ($c->pk && empty($value)) {
                throw new ORM_DB_Exception("{$c->name} is key and cannot be null!");
            }
            $columns_name[] = $c->name;
            $sql_value .= ($item_count > 0 ? ',' : '') . $this->escapeValue($value);
            $item_count++;
        }
        $keys = implode(',', $columns_name);
        $sql = "REPLACE INTO {$this->table_name}( $keys ) VALUES( $sql_value );";
        try {
            $this->db->update($sql);
            return $this->db->lastID();
        } catch (Exception $e) {
            $this->error = $e->getMessage();
            throw new ORM_DB_Exception($e->getMessage() . " sql:" . $sql);
        }
    }

    function escapeValue($value)
    {
        if (is_string($value)) {
            $value = $this->db->escape($value);
            $rtn = "'" . $value . "'";
        } else {
            $rtn = $value;
        }
        return $rtn;
    }

    function order_by($order)
    {
        foreach ($order as $o => $asc) {
            if (is_numeric($o)) {
                $this->order[$asc] = 'ASC';
            } else {
                if (in_array($asc, array('asc', 'desc', 'ASC', 'DESC'))) {
                    $this->order[$o] = $asc;
                }
            }
        }
        return $this;
    }

    function limit($num)
    {
        if (is_numeric($num)) {
            $this->limit = $num;
        }
        return $this;
    }

    /**
     * 根据where条件获取某些数据
     * @param $where
     * @param null $cols
     * @return array|boolean
     */
    function get($where, $cols = null)
    {
        $columns = $this->getColumn();
        $column_names = $this->getColumnNames();
        $sql_where = '';
        $count_where = 0;
        foreach ($columns as $v) {
            if (isset($where[$v->name])) {
                $value = $where[$v->name];
                $count_where++;
                if (is_array($value) && !empty($value)) {
                    foreach ($value as $single_key => $single_value) {
                        switch ($single_key) {
                            case 'like':
                                $sql_where .= " {$v->name} LIKE " . $this->escapeValue($value['like']) . " AND";
                                break;
                            case 'ne':
                                if (is_array($value['ne'])) {
                                    foreach ($value['ne'] as $neValue) {
                                        $sql_where .= " {$v->name} != " . $this->escapeValue($neValue) . " AND";
                                    }
                                } else {
                                    $sql_where .= " {$v->name} != " . $this->escapeValue($value['ne']) . " AND";
                                }
                                break;
                            case 'in':
                                if (is_array($value['in'])) {
                                    if (count($value['in'])) {
                                        $in = array();
                                        foreach ($value['in'] as $val) {
                                            $in[] = $this->escapeValue($val);
                                        }
                                        $sql_where .= " {$v->name} IN (" . implode(',', $in) . ") AND";
                                    }
                                } else {
                                    $sql_where .= " {$v->name} = " . $this->escapeValue($value['in']) . " AND";
                                }
                                break;
                            case 'not_in':
                                if (is_array($value['not_in'])) {
                                    if (count($value['not_in'])) {
                                        $not_in = array();
                                        foreach ($value['not_in'] as $val) {
                                            $not_in[] = $this->escapeValue($val);
                                        }
                                        $sql_where .= " {$v->name} NOT IN (" . implode(',', $not_in) . ") AND";
                                    }
                                } else {
                                    $sql_where .= " {$v->name} != " . $this->escapeValue($value['not_in']) . " AND";
                                }
                                break;
                            case 'is_null':
                                $sql_where .= " {$v->name} IS NULL AND";
                                break;
                            case 'is_not_null':
                                $sql_where .= " {$v->name} IS NOT NULL AND";
                                break;
                            case 'gt':
                                $sql_where .= " {$v->name} > " . $this->escapeValue($value['gt']) . " AND";
                                break;
                            case 'lt':
                                $sql_where .= " {$v->name} < " . $this->escapeValue($value['lt']) . " AND";
                                break;
                            case 'egt':
                                $sql_where .= " {$v->name} >= " . $this->escapeValue($value['egt']) . " AND";
                                break;
                            case 'elt':
                                $sql_where .= " {$v->name} <= " . $this->escapeValue($value['elt']) . " AND";
                                break;
                        }
                    }
                } else {
                    $sql_where .= " {$v->name} = " . $this->escapeValue($value) . " AND";
                }
            }
        }

        // 去掉AND
        if ($count_where > 0) {
            $sql_where = " WHERE " . substr($sql_where, 0, -3);
        }

        // 确定要查询的列
        $cols_query = array();
        if ($cols == null) {
            $cols_query = $column_names;
        } else {
            foreach ($cols as $v) {
                if (preg_match("/\\s/", $v)) {
                    $tmp = preg_split("/\\s+/", $v);
                    $mysql_keys = array("DISTINCT", 'distinct');
                    if (in_array($tmp[0], $mysql_keys) && in_array($tmp[1], $column_names)) {
                        $cols_query[] = $v;
                    }
                } else {
                    if (in_array($v, $column_names)) {
                        $cols_query[] = $v;
                    }
                }

            }
        }

        if (empty($cols_query)) {
            $this->error = "select no columns ";
            return false;
        }

        $sql_query = implode(',', $cols_query);
        $sql = " SELECT $sql_query FROM {$this->table_name} $sql_where ";

        if (!empty($this->order)) {
            $sql .= " ORDER BY ";
            $index = 0;
            foreach ($this->order as $o => $asc) {
                $sql .= ($index == 0 ? '' : ',') . $o . " " . $asc;
                $index++;
            }
            $this->order = array();
        }
        if ($this->limit > 0) {
            $sql .= " LIMIT " . intval($this->limit);
            $this->limit = 0;
        }
        $this->last_sql = $sql;
        return $this->db->getAll($sql);
    }

    /**
     * 获取所有的数据
     * @param null $cols
     * @return bool
     */
    function getAll($cols = null)
    {
        $column_names = $this->getColumnNames();

        //确定要查询的列
        $cols_query = array();
        if ($cols == null) {
            $cols_query = $column_names;
        } else {
            foreach ($cols as $v) {
                if (preg_match("/\\s/", $v)) {
                    $tmp = preg_split("/\\s+/", $v);
                    $mysql_keys = array("DISTINCT", 'distinct');
                    if (in_array($tmp[0], $mysql_keys) && in_array($tmp[1], $column_names)) {
                        $cols_query[] = $v;
                    }
                } else {
                    if (in_array($v, $column_names)) {
                        $cols_query[] = $v;
                    }
                }
            }
        }

        if (empty($cols_query)) {
            $this->error = "select no columns ";
            return false;
        }

        $sql_query = implode(',', $cols_query);
        $sql = " SELECT $sql_query FROM {$this->table_name}";

        if (!empty($this->order)) {
            $sql .= " ORDER BY ";
            $index = 0;
            foreach ($this->order as $o => $asc) {
                $sql .= ($index == 0 ? '' : ',') . $o . " " . $asc;
                $index++;
            }
            $this->order = array();
        }
        if ($this->limit > 0) {
            $sql .= " LIMIT " . intval($this->limit);
            $this->limit = 0;
        }
        $this->last_sql = $sql;
        return $this->db->getAll($sql);
    }

    /**
     * 根据where条件获取一条数据
     * @param $where
     * @param null $cols
     * @return bool|mixed
     */
    function getOne($where, $cols = null)
    {
        $data = $this->limit(1)->get($where, $cols);
        if (empty($data)) {
            return false;
        } else {
            return $data[0];
        }
    }

    /**
     * 获取字段名
     * @return array
     */
    function getColumnNames()
    {
        $columns = $this->getColumn();
        $colName = array();
        foreach ($columns as $c) {
            $colName[] = $c->name;
        }
        return $colName;
    }

    function getArrayFromPost($post)
    {
        $columns = $this->getColumn(false);
        $model = array();
        foreach ($columns as $c) {
            if (isset($post[$c->oname])) {
                $model[$c->oname] = trim($post[$c->oname]);
            }
        }
        return $model;
    }
}

if (!class_exists('ORM_DB_Exception')) {
    class ORM_DB_Exception extends Exception
    {

    }
}

if (!class_exists('Orm_Column')) {
    class Orm_Column
    {
        public $name;
        public $oname;
        public $type;
        public $pk;
        public $nullable;
        public $default;
        public $extra;

        function __construct($item = null)
        {
            if (!empty($item)) {
                if ($this->checkKeyName($item['Field'])) {
                    $this->name = '`' . $item['Field'] . '`';
                } else {
                    $this->name = $item['Field'];
                }
                $this->oname = $item['Field'];
                $this->type = $item['Type'];
                $this->nullable = $item['Null'] == "YES" ? true : false;
                $this->pk = $item['Key'] == "PRI" ? true : false;
                $this->default = $item['Default'];
                $this->extra = $item['Extra'];
            }
        }

        function checkKeyName($name)
        {
            $keys = array(
                'desc'
            );
            if (in_array($name, $keys)) {
                return true;
            } else {
                return false;
            }
        }
    }
}
if (!class_exists('orm_cache_null')) {
    class Orm_Cache_Null
    {
        function getData()
        {
        }

        function setData()
        {
        }
    }
}
